﻿using DBCook.Common;
using DBCook.Models;
using DBCook.Services;
using DBUtil.MetaData;
using DBUtil.Provider.SqlServer.MetaData;
using DotNetCommon.Data;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Hosting;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace DBCook.Controllers
{
    [ApiController]
    [Route("api/[controller]/[action]")]
    public class ManageController : ControllerBase
    {
        private readonly ManageService manageService;
        private readonly IHostEnvironment hostEnvironment;

        public ManageController(ManageService manageService, IHostEnvironment hostEnvironment)
        {
            this.manageService = manageService;
            this.hostEnvironment = hostEnvironment;
        }

        /// <summary>
        /// 获取所有的表
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        [HttpGet]
        public Result<List<SqlServerTable>> GetAllTables(string schema, string table)
        {
            return manageService.GetAllTables(schema, table);
        }

        /// <summary>
        /// 获取所有的存储过程
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="proc"></param>
        /// <returns></returns>
        [HttpGet]
        public Result<List<SqlServerProcedure>> GetAllProcs(string schema, string proc)
        {
            return manageService.GetAllProcs(schema, proc);
        }

        /// <summary>
        /// 获取所有的函数
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        [HttpGet]
        public Result<List<SqlServerFunction>> GetAllFuncs(string schema, string func)
        {
            return manageService.GetAllFuncs(schema, func);
        }

        /// <summary>
        /// 获取所有的序列
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="seq"></param>
        /// <returns></returns>
        [HttpGet]
        public Result<List<SqlServerSequence>> GetAllSeqs(string schema, string seq)
        {
            return manageService.GetAllSeqs(schema, seq);
        }

        /// <summary>
        /// 返回所有视图
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        [HttpGet]
        public Result<List<SqlServerView>> GetAllViews(string schema, string view)
        {
            return manageService.GetAllViews(schema, view);
        }

        /// <summary>
        /// 获取表结构详情
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<SqlServerTable> GetTableDetail([FromForm] string schema, [FromForm] string table)
        {
            return manageService.GetTableDetail(schema, table);
        }

        /// <summary>
        /// 获取视图结构详情
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<SqlServerView> GetViewDetail([FromForm] string schema, [FromForm] string view)
        {
            return manageService.GetViewDetail(schema, view);
        }

        /// <summary>
        /// 获取表数据
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="table"></param>
        /// <param name="orderKey"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        [HttpPost]
        public ResultPage<Dictionary<string, object>> GetTableData([FromForm] string schema, [FromForm] string table, [FromForm] string orderKey, [FromForm] int pageIndex, [FromForm] int pageSize)
        {
            return manageService.GetTableData(schema, table, orderKey, pageIndex, pageSize);
        }

        /// <summary>
        /// 获取视图数据
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="table"></param>
        /// <param name="orderKey"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        [HttpPost]
        public ResultPage<Dictionary<string, object>> GetViewData([FromForm] string schema, [FromForm] string view, [FromForm] string orderKey, [FromForm] int pageIndex, [FromForm] int pageSize)
        {
            return manageService.GetViewData(schema, view, orderKey, pageIndex, pageSize);
        }

        /// <summary>
        /// 获取建表语句
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> GetCreateTableSql([FromForm] string schema, [FromForm] string table)
        {
            return manageService.GetCreateTableSql(schema, table);
        }

        /// <summary>
        /// 获取新建存储过程语句
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="proc"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> GetCreateProcSql([FromForm] string schema, [FromForm] string proc)
        {
            return manageService.GetCreateProcSql(schema, proc);
        }

        /// <summary>
        /// 获取新建函数语句
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> GetCreateFuncSql([FromForm] string schema, [FromForm] string func)
        {
            return manageService.GetCreateFuncSql(schema, func);
        }

        /// <summary>
        /// 获取创建视图语句
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> GetCreateViewSql([FromForm] string schema, [FromForm] string view)
        {
            return manageService.GetCreateViewSql(schema, view);
        }

        /// <summary>
        /// 获取创建序列语句
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="seq"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> GetCreateSeqSql([FromForm] string schema, [FromForm] string seq)
        {
            return manageService.GetCreateSeqSql(schema, seq);
        }

        /// <summary>
        /// 获取插入表的Insert语句
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="table"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<(long count, string sql, long total)> GetInsertTableSql([FromForm] string schema, [FromForm] string table, [FromForm] int count)
        {
            return manageService.GetInsertTableSql(schema, table, count);
        }

        /// <summary>
        /// 获取插入视图的sql语句(视图可以插入?)
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="view"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<(long count, string sql, long total)> GetInsertViewSql([FromForm] string schema, [FromForm] string view, [FromForm] int count)
        {
            return manageService.GetInsertViewSql(schema, view, count);
        }

        /// <summary>
        /// 下载生成的表的Insert语句
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        [HttpGet]
        public IActionResult DownloadInsertSql([FromQuery] string schema, [FromQuery] string table)
        {
            var file = manageService.CreateInsertFile(schema, table);
            return PhysicalFile(file, "application/octet-stream");
        }

        /// <summary>
        /// 下载生成的视图的Insert语句
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        [HttpGet]
        public IActionResult DownloadInsertViewSql([FromQuery] string schema, [FromQuery] string view)
        {
            var file = manageService.CreateInsertViewFile(schema, view);
            return PhysicalFile(file, "application/octet-stream");
        }

        [HttpPost]
        public Result<string> TestConnect([FromForm] DbInfo dbInfo)
        {
            return manageService.TestConnect(dbInfo);
        }

        [HttpPost]
        public Result<string> AddConnect([FromForm] DbInfo dbInfo)
        {
            return manageService.AddConnect(dbInfo);
        }

        [HttpPost]
        public Result<string> DeleteConnect([FromForm] string guid)
        {
            return manageService.DeleteConnect(guid);
        }

        /// <summary>
        /// 根据表自动生成代码
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> GetTableCode([FromForm] string schema, [FromForm] string table)
        {
            return manageService.GetTableCode(schema, table);
        }

        /// <summary>
        /// 根据视图自动生成代码
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> GetViewCode([FromForm] string schema, [FromForm] string view)
        {
            return manageService.GetViewCode(schema, view);
        }

        /// <summary>
        /// 导出建表sql
        /// </summary>
        /// <param name="tables"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> GetCreateTableSqlBatch([FromBody] List<Table> tables)
        {
            return manageService.GetCreateTableSqlBatch(tables);
        }

        /// <summary>
        /// 导出创建视图sql
        /// </summary>
        /// <param name="views"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> GetCreateViewSqlBatch([FromBody] List<View> views)
        {
            return manageService.GetCreateViewSqlBatch(views);
        }

        /// <summary>
        /// 导出创建函数sql
        /// </summary>
        /// <param name="funcs"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> GetCreateFuncSqlBatch([FromBody] List<Function> funcs)
        {
            return manageService.GetCreateFuncSqlBatch(funcs);
        }

        /// <summary>
        /// 导出创建存储过程sql
        /// </summary>
        /// <param name="procs"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> GetCreateProcSqlBatch([FromBody] List<Procedure> procs)
        {
            return manageService.GetCreateProcSqlBatch(procs);
        }

        /// <summary>
        /// 导出创建序列sql
        /// </summary>
        /// <param name="seqs"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> GetCreateSeqSqlBatch([FromBody] List<Sequence> seqs)
        {
            return manageService.GetCreateSeqSqlBatch(seqs);
        }

        [HttpGet]
        public IActionResult DownLoadExportSql(string filename)
        {
            var path = Path.Combine(hostEnvironment.ContentRootPath, "export-sqls", filename);
            if (!System.IO.File.Exists(path)) return Ok(Result.NotOk("未找到文件!"));
            return PhysicalFile(path, "application/octet-stream");
        }

        [HttpPost]
        public Result<string> GetCreateInsertTableSqlBatch([FromBody] List<Table> tables)
        {
            return manageService.GetCreateTableSqlBatch(tables, true);
        }

        /// <summary>
        /// 更新表说明
        /// </summary>
        /// <param name="name"></param>
        /// <param name="schemaName"></param>
        /// <param name="desc"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> UpdateTableDescription([FromForm] string name, [FromForm] string schemaName, [FromForm] string desc)
        {
            return manageService.UpdateTableDescription(name, schemaName, desc);
        }

        /// <summary>
        /// 更新视图说明
        /// </summary>
        /// <param name="name"></param>
        /// <param name="schemaName"></param>
        /// <param name="desc"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> UpdateViewDescription([FromForm] string name, [FromForm] string schemaName, [FromForm] string desc)
        {
            return manageService.UpdateViewDescription(name, schemaName, desc);
        }

        /// <summary>
        /// 更新函数说明
        /// </summary>
        /// <param name="name"></param>
        /// <param name="schemaName"></param>
        /// <param name="desc"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> UpdateFuncDescription([FromForm] string name, [FromForm] string schemaName, [FromForm] string desc)
        {
            return manageService.UpdateFuncDescription(name, schemaName, desc);
        }

        /// <summary>
        /// 更新存储过程说明
        /// </summary>
        /// <param name="name"></param>
        /// <param name="schemaName"></param>
        /// <param name="desc"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> UpdateProcDescription([FromForm] string name, [FromForm] string schemaName, [FromForm] string desc)
        {
            return manageService.UpdateProcDescription(name, schemaName, desc);
        }

        /// <summary>
        /// 更新表的列说明
        /// </summary>
        /// <param name="name"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="desc"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> UpdateColumnDescription([FromForm] string name, [FromForm] string schemaName, [FromForm] string tableName, [FromForm] string desc)
        {
            return manageService.UpdateColumnDescription(name, schemaName, tableName, desc);
        }

        /// <summary>
        /// 更新视图的列说明
        /// </summary>
        /// <param name="name"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="desc"></param>
        /// <returns></returns>
        [HttpPost]
        public Result<string> UpdateViewColumnDescription([FromForm] string name, [FromForm] string schemaName, [FromForm] string viewName, [FromForm] string desc)
        {
            return manageService.UpdateViewColumnDescription(name, schemaName, viewName, desc);
        }

    }
}
